﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using GDCal.vo;
using System.Data;

namespace GDCal.dao
{
    public class LandPriceDao : AbstractDao
    {
        public static ArrayList filter(Int32 year)
        {
            ArrayList ret = new ArrayList();
            LandPriceVO filterObj = new LandPriceVO();
            filterObj.setYear(year);
            SqlDataReader rs;
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT distinct city FROM Land_Price WHERE year = " + year + " ORDER BY city ASC";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            while (rs.Read())
            {
                ret.Add(rs.GetString(0));
            }

            oConn.Close();
            return ret;
        }

        public static ArrayList filter(LandPriceVO filterObj)
        {
            ArrayList ret = new ArrayList();
            SqlDataReader rs;
            LandPriceVO temp = null;
            SqlConnection oConn = getConnection();

            String whereClause = buildWhereClause(filterObj);
            String strSQL = "SELECT id, street_type_id, price, year, note, city FROM Land_Price";
            if (whereClause != null)
                strSQL += whereClause;
            strSQL += " ORDER BY id ASC";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            while (rs.Read())
            {
                temp = new LandPriceVO();
                temp.setId(rs.GetInt32(0));
                temp.setStreetTypeId(rs.GetInt32(1));
                temp.setPrice(rs.GetDecimal(2));
                temp.setYear(rs.GetInt32(3));
                temp.setNote(rs.GetString(4));
                temp.setCity(rs.GetString(5));
                ret.Add(temp);
            }

            oConn.Close();
            return ret;
        }

        private static String buildWhereClause(LandPriceVO filterObj)
        {
            String ret = "";
            Boolean addAnd = false;
            if (filterObj != null)
            {
                if (filterObj.getId() > 0)
                {
                    ret += " id = " + filterObj.getId();
                    addAnd = true;
                }

                if (filterObj.getStreetTypeId() > 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " street_type_id = " + filterObj.getStreetTypeId();
                }

                if (filterObj.getPrice() > 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " price = " + filterObj.getPrice();
                }

                if (filterObj.getYear() > 0)
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " year = " + filterObj.getYear();
                }

                if (filterObj.getNote() != null && !"".Equals(filterObj.getNote()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " note like (N'%" + filterObj.getNote() + "%')";
                }

                if (filterObj.getCity() != null && !"".Equals(filterObj.getCity()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " city like (N'%" + filterObj.getCity() + "%')";
                }

                if (!"".Equals(ret))
                {
                    ret = " WHERE" + ret;
                }
            }
            return ret;
        }

        public static int delLandPrice(Int32 delId)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "DELETE FROM Land_Price WHERE id = " + delId;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static LandPriceVO getLandPrice(int id)
        {
            SqlDataReader rs;
            LandPriceVO ret = null;
            if (id == 0)
            {
                ret = new LandPriceVO();
                return ret;
            }
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT street_type_id, price, year, note, city FROM Land_Price WHERE id = " + id;
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rs.Read())
            {
                ret = new LandPriceVO();
                ret.setId(id);
                ret.setStreetTypeId(rs.GetInt32(0));
                ret.setPrice(rs.GetDecimal(1));
                ret.setYear(rs.GetInt32(2));
                ret.setNote(rs.GetString(3));
                ret.setCity(rs.GetString(4));
            }

            oConn.Close();
            return ret;
        }

        public static LandPriceVO getLandPriceByUniqueKeySet(Int32 streetTypeId, Int32 year, String city)
        {
            SqlDataReader rs;
            LandPriceVO ret = null;
            if (year == 0 || streetTypeId == 0)
            {
                ret = new LandPriceVO();
                return ret;
            }
            SqlConnection oConn = getConnection();
            
            String strSQL = "SELECT id, price, note FROM Land_Price WHERE street_type_id = " + streetTypeId + " AND year = " + year + " AND city = (N'" + city + "')";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rs.Read())
            {
                ret = new LandPriceVO();
                ret.setId(rs.GetInt32(0));
                ret.setStreetTypeId(streetTypeId);
                ret.setPrice(rs.GetDecimal(1));
                ret.setYear(year);
                ret.setNote(rs.GetString(2));
                ret.setNote(city);
            }

            oConn.Close();
            return ret;
        }

        public static int updateLandPrice(LandPriceVO lpVO)
        {
            int ret;
            SqlConnection oConn = getConnection();

            String strSQL = "UPDATE Land_Price SET "
                            + "street_type_id=" + lpVO.getStreetTypeId() + ", "
                            + "price=" + lpVO.getPrice() + ", "
                            + "year=" + lpVO.getYear() + ", "
                            + "note=(N'" + lpVO.getNote() + "'), "
                            + "city=(N'" + lpVO.getCity() + "') "
                            + "WHERE id = " + lpVO.getId();

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static int insertLandPrice(LandPriceVO lpVO)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "INSERT INTO Land_Price(street_type_id, price, year, note, city) VALUES ("
                            + lpVO.getStreetTypeId() + ", "
                            + lpVO.getPrice() + ", "
                            + lpVO.getYear() + ", "
                            + "(N'" + lpVO.getNote() + "'), "
                            + "(N'" + lpVO.getCity() + "') )";

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }
    }
}
